package fr.enseirb.mycub.db;

import java.util.ArrayList;
import org.json.JSONException;

import fr.enseirb.mycub.db.structure.InternetDAO;
import android.database.DatabaseUtils.InsertHelper;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteStatement;
import android.provider.BaseColumns;

public class InternetDBHelper {

	public static void insertInternets(SQLiteDatabase mDb,
			ArrayList<InternetDAO> jsonInternet) throws JSONException,
			SQLiteException {
		InsertHelper InternetInsertHelper = new InsertHelper(mDb,
				InternetDBConstants.INTERNET_TABLE);
		final int idColumn = InternetInsertHelper
				.getColumnIndex(BaseColumns._ID);
		final int nameColumn = InternetInsertHelper
				.getColumnIndex(InternetDBConstants.KEY_NAME);
		final int openColumn = InternetInsertHelper
				.getColumnIndex(InternetDBConstants.KEY_OPEN);
		final int addressColumn = InternetInsertHelper
				.getColumnIndex(InternetDBConstants.KEY_ADDRESS);
		final int latitudeColumn = InternetInsertHelper
				.getColumnIndex(InternetDBConstants.KEY_LATITUDE);
		final int longitudeColumn = InternetInsertHelper
				.getColumnIndex(InternetDBConstants.KEY_LONGITUDE);
		final int favoriteColumn = InternetInsertHelper
				.getColumnIndex(InternetDBConstants.KEY_FAVORITE);
		final int internetColumn = InternetInsertHelper
				.getColumnIndex(InternetDBConstants.KEY_FREE);
		final int size = jsonInternet.size();		
		final String sql = "INSERT INTO "
				+ InternetDBConstants.INTERNET_TABLE
				+ " VALUES (?,?,?,?,?,?,?,?);";

		InternetInsertHelper.close();
		
		try {
			mDb.beginTransaction();
			SQLiteStatement insert = mDb.compileStatement(sql);
			int id;
			String name;
			for (int i = 0; i < size; i++) {
				InternetDAO jsonItem = jsonInternet.get(i);
				id = jsonItem.getId();
				name = jsonItem.getName();
				insert.bindLong(idColumn, id);
				insert.bindString(nameColumn, name);
				insert.bindLong(openColumn, jsonItem.isOpen() ? 1 : 0);
				insert.bindString(addressColumn, jsonItem.getAddress());
				insert.bindLong(latitudeColumn,
						(int) (jsonItem.getmLatitude() * 1E6));
				insert.bindLong(longitudeColumn,
						(int) (jsonItem.getmLongitude() * 1E6));
				insert.bindLong(favoriteColumn, 0); // Favorite
				insert.bindLong(internetColumn, jsonItem.isFree() ? 1 : 0);
				insert.executeInsert();

			}
			mDb.setTransactionSuccessful();
		} catch (SQLException e) {
			throw e;
		} finally {
			mDb.endTransaction();
		}
	}


	public static void cleanAndInsertInternets(SQLiteDatabase mDb,
			ArrayList<InternetDAO> jsonInternet) throws JSONException {
		mDb.delete(InternetDBConstants.INTERNET_TABLE, null, null);
		insertInternets(mDb, jsonInternet);
	}

	public static int getInternetCount(SQLiteDatabase mDb) throws SQLException {
		Cursor cursor = mDb.rawQuery("SELECT COUNT(*) AS count FROM "
				+ InternetDBConstants.INTERNET_TABLE,null);
		cursor.moveToNext();
		int count = cursor.getInt(0);
		cursor.close();
		return count;
	}

	public static ArrayList<Cursor> getFilteredInternetCursor(SQLiteDatabase mDb,String[] projection,
			String where,
			String orderBy) {
		String nWhere;
		nWhere = where;
		ArrayList<Cursor> cursors = new ArrayList<Cursor>();
		cursors.add( mDb.query(InternetDBConstants.INTERNET_TABLE, projection, nWhere,
				null, null, null,
				orderBy));
		return cursors;
	}

	public static Cursor getInternet(SQLiteDatabase mDb, int id, String[] columns) throws SQLException {
		Cursor cursor = mDb.query(true, InternetDBConstants.INTERNET_TABLE, columns,
				BaseColumns._ID + " = ?",
				new String[] {
				String.valueOf(id) },
				null, null, null, null);
		if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
			throw new SQLException("No Internet found with ID " + id);
		}
		return cursor;
	}
}
